SQL: how to get all the distinct characters in a column, across all rows

您所在的位置：网站首页 › select distinct › SQL: how to get all the distinct characters in a column, across all rows

SQL: how to get all the distinct characters in a column, across all rows

#SQL: how to get all the distinct characters in a column, across all rows| 来源: 网络整理| 查看: 265

Use this (shall work on any CTE-capable RDBMS):

select x.v into prod from (values('product1'),('widget2'),('nicknack3')) as x(v);

Test Query:

with a as ( select v, '' as x, 0 as n from prod union all select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v) ) select v, x, n from a -- where n > 0 order by v, n option (maxrecursion 0)

Final Query:

with a as ( select v, '' as x, 0 as n from prod union all select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v) ) select distinct x from a where n > 0 order by x option (maxrecursion 0)

Oracle version:

with a(v,x,n) as ( select v, '' as x, 0 as n from prod union all select v, substr(v,n+1,1) as x, n+1 as n from a where n < length(v) ) select distinct x from a where n > 0

Here's a query that returns each character as a separate row, along with the number of occurrences. Assuming your table is called 'Products'

WITH ProductChars(aChar, remain) AS ( SELECT LEFT(productName,1), RIGHT(productName, LEN(productName)-1) FROM Products WHERE LEN(productName)>0 UNION ALL SELECT LEFT(remain,1), RIGHT(remain, LEN(remain)-1) FROM ProductChars WHERE LEN(remain)>0 ) SELECT aChar, COUNT(*) FROM ProductChars GROUP BY aChar

To combine them all to a single row, (as stated in the question), change the final SELECT to

SELECT aChar AS [text()] FROM (SELECT DISTINCT aChar FROM ProductChars) base FOR XML PATH('')

The above uses a nice hack I found here, which emulates the GROUP_CONCAT from MySQL.

The first level of recursion is unrolled so that the query doesn't return empty strings in the output.

Given that your column is varchar, it means it can only store characters from codes 0 to 255, on whatever code page you have. If you only use the 32-128 ASCII code range, then you can simply see if you have any of the characters 32-128, one by one. The following query does that, looking in sys.objects.name:

with cteDigits as ( select 0 as Number union all select 1 as Number union all select 2 as Number union all select 3 as Number union all select 4 as Number union all select 5 as Number union all select 6 as Number union all select 7 as Number union all select 8 as Number union all select 9 as Number) , cteNumbers as ( select U.Number + T.Number*10 + H.Number*100 as Number from cteDigits U cross join cteDigits T cross join cteDigits H) , cteChars as ( select CHAR(Number) as Char from cteNumbers where Number between 32 and 128) select cteChars.Char as [*] from cteChars cross apply ( select top(1) * from sys.objects where CHARINDEX(cteChars.Char, name, 0) > 0) as o for xml path('');

【本文地址】

SQL: how to get all the distinct characters in a column, across all rows

SQL: how to get all the distinct characters in a column, across all rows

今日新闻

推荐新闻